﻿--1 MANAGERS
SELECT DISTINCT REGION, P.ACCT_MGR, P.ACCT_MGR AS x, 2 AS LVL, 2 AS ORD
FROM         SCORE_ACC_PROPERTIES.PROPERTIES P
INNER JOIN MANAGERS_WITH_SALES_VIEW V ON V.ACCT_MGR=P.ACCT_MGR
GROUP BY REGION, P.ACCT_MGR
UNION
SELECT DISTINCT REGION, ' ' + REGION AS Expr1, REGION AS Expr3, 0 AS Expr4, 0 AS Expr5
FROM         SCORE_ACC_PROPERTIES.PROPERTIES AS PROPERTIES_1
ORDER BY REGION, ORD, ACCT_MGR, LVL
;
--2 STATES
SELECT	DISTINCT ST, STATE, REGION, 2 AS ORD, CASE ST WHEN ' ALL STATES' THEN 0 ELSE 2 END AS LVL
FROM MARKET_ABBRS
UNION
SELECT DISTINCT REGION, ' ' + REGION AS Expr1, REGION AS Expr3, 1 AS Expr4, 1 AS Expr5
FROM         SCORE_ACC_PROPERTIES.PROPERTIES AS PROPERTIES_1
ORDER BY REGION, ORD, ST, LVL
;
--3 PORTFOLIOS
SELECT PORTFOLIO_NM AS TXT, PORTFOLIO_NM AS VAL
FROM PORTFOLIOS_WITH_SALES_VIEW
ORDER BY TXT
;
--4 MARKETS
SELECT  MKT , MARKET, region, 2 AS lvl, 2 AS ord
FROM  MARKET_ABBRS
WHERE LEN(MARKET)>0
UNION
SELECT DISTINCT REGION, ' ' + REGION AS Expr1, REGION AS Expr3, 0 AS Expr4, 0 AS Expr5
FROM         SCORE_ACC_PROPERTIES.PROPERTIES AS PROPERTIES_1
ORDER BY REGION, ORD, MKT, LVL
;
--5 REGIONS
SELECT	DISTINCT REGION AS txt, REGION AS val
FROM MARKET_ABBRS
ORDER BY txt
;